﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DBUtility;

namespace DAL
{
    public class AdjustPositionDAL
    {
        //获取库位中条码列表
        public DataSet GetAdjustPosition(string CWPID)
        {
            DataSet ds = new DataSet();
            StringBuilder sql = new StringBuilder();


            sql.Append("  select ROW_NUMBER() over(order by PlaceBarCode, Floor DESC) row ,(case when (cast(PkgsStatus as varchar(10))=1 or cast(PkgsStatus as varchar(10)) =88) then '■-' when cast(PkgsStatus as varchar(10))=10 then '◆-' else '' end)+ PkgsBarCode as PkgsBarCode,PlaceBarCode,Floor,FName PlaceName,HelperCode ");
            sql.Append("  from stk_Package a");
            sql.Append("  left join t_StockPlace b on a.PosiID=b.FSPID");
            sql.Append("  where PosiID ='" + CWPID + "'  and a.PkgsStatus in (0,1,88,33)");

            try
            {
                ds = DbHelperSQL.Query(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        //根据库位编码获取库位ID、名称
        public bool GetPIDbyPlaceCode(string PlaceCode, out string CWPID, out string PlaceName)
        {
            bool result = false;
            DataSet ds = new DataSet();
            StringBuilder sql = new StringBuilder();
            CWPID = "";
            PlaceName = "";

            sql.Append("select FSPID,FName PlaceName from t_StockPlace where PlaceBarCode ='" + PlaceCode + "'");

            try
            {
                ds = DbHelperSQL.Query(sql.ToString());
                if (ds != null && ds.Tables[0].Rows.Count>0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        CWPID = dr["FSPID"].ToString(); if (CWPID == null || CWPID == "") { return false; }
                        PlaceName = dr["PlaceName"].ToString(); if (PlaceName == null || PlaceName == "") { return false; } 
                    }
                    result = true;
                }
            } 
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        //根据包装条码查找其所在的库位编码、库位名称、层
        public bool GetJPIDbyPkgsBarCode(string PkgsBarCode, out string JPlaceCode, out string JPID, out int Floor)
        {
           bool result = false;
           DataSet ds = new DataSet();
           StringBuilder sql = new StringBuilder();
            JPlaceCode="";
            JPID ="";
            Floor=0;


            sql.Append(" select b.PlaceBarCode PlaceBarCode,b.FSPID FSPID,a.Floor Floor,b.FName as kwname from stk_Package a ");
            sql.Append(" left join t_StockPlace b on a.PosiID = b.FSPID");
            sql.Append("  where PkgsBarCode ='" + PkgsBarCode + "'   and a.PkgsStatus not in (2,4,5,6,55,99,44,10) ");

            try
            {
                ds = DbHelperSQL.Query(sql.ToString());
                if (ds != null && ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        JPlaceCode = dr["kwname"].ToString(); if (JPlaceCode == null || JPlaceCode == "") { return false; }
                        JPID = dr["FSPID"].ToString(); if (JPID == null || JPID == "") { return false; }
                        Floor = int.Parse(dr["Floor"].ToString()); 
                    }
                    result = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        
          return result; 
        }

        //更新包装条码的库位、层
        public string UPdateSTOCKFloor(string PkgsBarCode, string CWPID, int Floor)
       {
         StringBuilder sql = new StringBuilder();
           int i = 0;
            string state = "";
           sql.Append("update  stk_Package set PosiID ='"+CWPID+"',Floor ='"+Floor+"' where PkgsBarCode ='"+PkgsBarCode+"'");
           try
           {
               i = DbHelperSQL.ExecuteSql(sql.ToString());
               if (i > 0)
               { state = "1"; }

           }
           catch (Exception ex)
           {
               throw ex;
           }
           return state;
        }

    }
}
